package com.yellowpage.bigdata.service;

import java.util.List;
import java.util.Map;

import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;

import com.yellowpage.bigdata.data.NspOnofflogImeiDaySum;
import com.yellowpage.bigdata.dao.NspOnofflogImeiDaySumDao;

public class NspOnofflogImeiDaySumServiceImpl extends BaseServiceImpl implements
		NspOnofflogImeiDaySumService {
	private NspOnofflogImeiDaySumDao nspOnofflogImeiDaySumDao;

	public void setNspOnofflogImeiDaySumDao(
			NspOnofflogImeiDaySumDao nspOnofflogImeiDaySumDao) {
		this.nspOnofflogImeiDaySumDao = nspOnofflogImeiDaySumDao;
	}

	/* Smooth generate Method */
	public NspOnofflogImeiDaySum getNspOnofflogImeiDaySum(Long id) {
		return nspOnofflogImeiDaySumDao.get(id);
	}

	public List<NspOnofflogImeiDaySum> getAllNspOnofflogImeiDaySum() {
		return nspOnofflogImeiDaySumDao.getAll();
	}

	public List<Map> queryNspOnofflogImeiDaySumByPage(Map queryMap,
			PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.LOG_DATE , a.APP_VERSION , a.OPERATOR , a.CHANNEL_NO , a.CREATE_USER , a.CREATE_TIME , a.NOTE , a.ONOFFLOG_IMEI_DAY_SUM_ID , a.IMEI_DAY_COUNT , "
								+ "a.IMEI_ALL_COUNT , a.IMEI_NEW_COUNT,a.NEXT_DAY_RETAINED as NEXT_DAY_RETAINED,a.THREE_DAY_RETAINED as THREE_DAY_RETAINED,a.SEVEN_DAY_RETAINED as SEVEN_DAY_RETAINED,a.THIRTY_DAY_RETAINED as THIRTY_DAY_RETAINED,"
								+ "(select b.NAME from OMC_DIC_TABLE b where b.VALUE = a.CHANNEL_NO and b.DIC_NAME='NSP_ONOFFLOG_CHANNEL_NO') as CHANNEL_NO_NAME  "
								+ "FROM NSP_ONOFFLOG_IMEI_DAY_SUM a WHERE 1=1 ")
				.addCondition(" and LOG_DATE >= :logDate ")
				.addCondition(" and a.LOG_DATE <= :query_logDate_end ")
				.addCondition(" and a.APP_VERSION = :query_appVersion ")
				.addCondition(" and a.OPERATOR = :query_operator ")
				.addCondition(" and a.CHANNEL_NO = :query_channelNo ");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	public List<Map> queryNspOnofflogImeiDayByPage(Map queryMap,
			PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"select DISTINCT b.LOG_DATE,b.APP_VERSION,"
								+ "d.cc as USENUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.OPERATOR='CT') as DXNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.OPERATOR='noCT') as NODXNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.CHANNEL_NO='android' and c.OPERATOR='A') as ANDROIDNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.CHANNEL_NO='101' and c.OPERATOR='A') as IOSNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.CHANNEL_NO not in('101','100','A')) as OTHERNUM,"
								+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.OPERATOR='A' and c.APP_VERSION=b.APP_VERSION and c.LOG_DATE=b.LOG_DATE and c.CHANNEL_NO='A')as IMEI_DAY_COUNT,"
								+ "(select imei_all_count from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.OPERATOR='A' and c.APP_VERSION=b.APP_VERSION and c.LOG_DATE=b.LOG_DATE and c.CHANNEL_NO='A') as imei_all_count,"
								+ "(select imei_new_count from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.OPERATOR='A' and c.APP_VERSION=b.APP_VERSION and c.LOG_DATE=b.LOG_DATE and c.CHANNEL_NO='A')as imei_new_count"
								+ " FROM NSP_ONOFFLOG_IMEI_DAY_SUM b,"
								+ "(select c.LOG_DATE, c.APP_VERSION, sum(c.REQUEST_COUNT) as cc from NSP_ONOFFLOG_REQUSET_SUM c group by c.LOG_DATE, c.APP_VERSION "
								+ "union all select c.LOG_DATE, 'A', sum(c.REQUEST_COUNT) as cc from NSP_ONOFFLOG_REQUSET_SUM c group by c.LOG_DATE) d"
								+ " WHERE 1=1 AND d.LOG_DATE=b.LOG_DATE and b.APP_VERSION<>'A' and d.APP_VERSION=b.APP_VERSION")
				.addCondition(" and b.LOG_DATE >= :logDate ")
				.addCondition(" and b.LOG_DATE <= :query_logDate_end ")
				.addCondition(" and b.APP_VERSION = :query_appVersion ");
		sqlAssistant.setTail(" order by b.APP_VERSION desc");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	public List<Map> queryNspOnofflogImeiDayByPage(Map queryMap) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"select DISTINCT b.LOG_DATE,b.APP_VERSION,"
								+ "d.cc as USENUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.OPERATOR='CT') as DXNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.OPERATOR='noCT') as NODXNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.CHANNEL_NO='android' and c.OPERATOR='A') as ANDROIDNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.CHANNEL_NO='101' and c.OPERATOR='A') as IOSNUM,"
								+ "(select sum(IMEI_DAY_COUNT) from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.LOG_DATE=b.LOG_DATE and c.APP_VERSION=b.APP_VERSION and c.CHANNEL_NO not in('101','100','A')) as OTHERNUM,"
								+ "(select IMEI_DAY_COUNT from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.OPERATOR='A' and c.APP_VERSION=b.APP_VERSION and c.LOG_DATE=b.LOG_DATE and c.CHANNEL_NO='A')as IMEI_DAY_COUNT,"
								+ "(select imei_all_count from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.OPERATOR='A' and c.APP_VERSION=b.APP_VERSION and c.LOG_DATE=b.LOG_DATE and c.CHANNEL_NO='A') as imei_all_count,"
								+ "(select imei_new_count from NSP_ONOFFLOG_IMEI_DAY_SUM c where c.OPERATOR='A' and c.APP_VERSION=b.APP_VERSION and c.LOG_DATE=b.LOG_DATE and c.CHANNEL_NO='A')as imei_new_count"
								+ " FROM NSP_ONOFFLOG_IMEI_DAY_SUM b,"
								+ "(select c.LOG_DATE, c.APP_VERSION, sum(c.REQUEST_COUNT) as cc from NSP_ONOFFLOG_REQUSET_SUM c group by c.LOG_DATE, c.APP_VERSION "
								+ "union all select c.LOG_DATE, 'A', sum(c.REQUEST_COUNT) as cc from NSP_ONOFFLOG_REQUSET_SUM c group by c.LOG_DATE) d"
								+ " WHERE 1=1 AND d.LOG_DATE=b.LOG_DATE and b.APP_VERSION<>'A' and d.APP_VERSION=b.APP_VERSION")
				.addCondition(" and b.LOG_DATE >= :logDate ")
				.addCondition(" and b.LOG_DATE <= :query_logDate_end ")
				.addCondition(" and b.APP_VERSION = :query_appVersion ");
		sqlAssistant
				.setTail(" GROUP BY b.LOG_DATE , b.APP_VERSION order by b.LOG_DATE , b.APP_VERSION");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				queryMap);

		return resultList;
	}

	public void saveNspOnofflogImeiDaySum(
			NspOnofflogImeiDaySum nspOnofflogImeiDaySum) {
		nspOnofflogImeiDaySumDao.save(nspOnofflogImeiDaySum);
	}

	public void updateNspOnofflogImeiDaySum(
			NspOnofflogImeiDaySum nspOnofflogImeiDaySum) {
		nspOnofflogImeiDaySumDao.update(nspOnofflogImeiDaySum);
	}

	public void delete(List deleteList) {
		nspOnofflogImeiDaySumDao.remove(deleteList);
	}

}